#! /usr/bin/python3
# -*- coding: UTF-8 -*-

if __name__ == "__main__":
	raise Exception("不支持从这里调用")

__version__ = "1.0"
__all__ = []

from hashlib import md5
from uuid import uuid4
from decimal import Decimal
import pandas as pd

from com.mousechsh.business.lib.model.MouseChshAppModel import MouseChshAppModel
from com.mousechsh.business.lib.MouseChshAuthorizationAnnotition import MouseChshAuthorizationAnnotition
from com.mousechsh.business.lib.model.MouseChshDomainModel import MouseChshDomainModel
from com.mousechsh.business.lib.model.MouseChshKeyValueModel import MouseChshKeyValueModel
from com.mousechsh.business.lib.model.MouseChshLoginModel import MouseChshLoginModel
from com.mousechsh.business.lib.model.MouseChshModuleModel import MouseChshModuleModel
from com.mousechsh.business.lib.model.MouseChshUserModel import MouseChshUserModel
from com.mousechsh.common.data.MouseChshDao import MouseChshDao
from com.mousechsh.common.data.json.MouseChshJson import mousechsh_json_from_json, mousechsh_json_to_json
from com.mousechsh.common.io.cache.MouseChshRedisCache import MouseChshRedisCache
from com.mousechsh.common.io.db.MouseChshPostgresql import MouseChshPostgresql
from com.mousechsh.common.io.file.MouseChshFile import mousechsh_file_write_binary
from com.mousechsh.common.middle.auth.MouseChshJwt import mousechsh_jwt_encode_string
from com.mousechsh.common.middle.conf.MouseChshConfiguration import mousechsh_configuration_get
from com.mousechsh.common.middle.http.MouseChshHttpHeader import mousechsh_http_header_accept_language_annotation, \
	mousechsh_http_header_authorization_annotation
from com.mousechsh.common.middle.http.MouseChshHttpRequest import MouseChshHttpRequest
from com.mousechsh.common.middle.http.MouseChshHttpMultiPartBody import MouseChshHttpMultiPartBody
from com.mousechsh.common.middle.rest_api.MouseChshRestApi import mousechsh_rest_api_annotation, mousechsh_rest_pager_api_annotation
from com.mousechsh.common.middle.router.MouseChshCorsAnnotition import mousechsh_cors_annotation
from com.mousechsh.common.middle.router.MouseChshHttpRouter import mousechsh_http_router_annotation
from com.mousechsh.common.util.MouseChshDateTime import MouseChshDateTime
from com.mousechsh.common.log.MouseChshLog import mousechsh_logging, mousechsh_logging_data, mousechsh_logging_exception
from com.mousechsh.common.util.MouseChshMath import mousechsh_math_gen_uuid


@mousechsh_http_router_annotation(method='GET', path='/')
@mousechsh_http_header_accept_language_annotation()
def index(url, request, response, *argsArr, **argsDict):
	response.get_header().set_content_type('html')
	response.set_body(
		'''
<!DOCTYPE html>
<html lang="zh-CN">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<title>工单服务器</title>
</head>
<body>
	工单服务器已经启动，请通过接口访问本服务。
</body>
</html>
'''
	)

@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/get/ticket')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
@mousechsh_rest_pager_api_annotation()
def GetWorkerTicket(params):

	pageSize = params['pageSize'] if 'pageSize' in params else 10
	pageIndex = ( params['pageIndex'] if 'pageIndex' in params else 1 )

	db = MouseChshPostgresql()
	db.connect()
	count = db.get(
		'''
SELECT
	COUNT(*) AS "Count"
FROM "A_WorkTickets"."T_WorkTicket"
		''', {
		}
	)
	data = db.get(
		'''
SELECT
	"C_TicketId" AS "TicketId",
	"C_FB2_TicketLevel" AS "TicketLevel",
	"C_FB2_TicketTitle" AS "TicketTitle"
FROM "A_WorkTickets"."T_WorkTicket"
ORDER BY "C_FB2_TicketCreate" DESC NULLS LAST
LIMIT %(pageSize)s OFFSET %(pageIndex)s
''', {
			'pageSize': pageSize,
			'pageIndex': ( ( pageIndex - 1 ) * pageSize if pageIndex - 1 > 0 else 0 )
		}
	)
	db.close()

	result = []
	for item in data['data']:
		obj = {}
		i = 0
		for col in data['meta']:
			obj[col.name] = item[i]
			i += 1
		result.append(obj)

	return {
		'data': result,
		'pageIndex': pageIndex,
		'pageSize': pageSize,
		'totalData': count['data'][0][0]
	}

@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/get/ticket/detail')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
@mousechsh_rest_api_annotation()
def GetWorkerTicketDetail(params):

	ticket_id = params['TicketId'] if params['TicketId'] else ''

	db = MouseChshPostgresql()
	db.connect()
	data = db.get(
		'''
SELECT
	"C_TicketId" AS "TicketId",
	"C_FB2_TicketCategory" AS "FB2_TicketCategory",
	"C_FB2_TicketLevel" AS "FB2_TicketLevel",
	"C_FB2_IsBreakBusiness" AS "FB2_IsBreakBusiness",
	"C_FB2_NetworkLevel" AS "FB2_NetworkLevel",
	"C_FB2_TicketTitle" AS "FB2_TicketTitle",
	"C_FB2_TicketContent" AS "FB2_TicketContent",
	"C_FB2_TicketStatus" AS "FB2_TicketStatus",
	"C_FB2_ReCreateTimes" AS "FB2_ReCreateTimes",
	"C_FB2_IsForceCloseOvertime" AS "FB2_IsForceCloseOvertime",
	"C_FB2_IsFree" AS "FB2_IsFree",
	"C_FB2_IsProjectLeave" AS "FB2_IsProjectLeave",
	"C_FB2_BaseStationId" AS "FB2_BaseStationId",
	"C_FB2_IsProcOvertime" AS "FB2_IsProcOvertime",
	"C_FB2_TicketProcess" AS "FB2_TicketProcess",
	"C_FB2_TicketReason" AS "FB2_TicketReason",
	"C_FB2_TicketReasonCategoryLv1" AS "FB2_TicketReasonCategoryLv1",
	"C_FB2_TicketReasonCategoryLv2" AS "FB2_TicketReasonCategoryLv2",
	"C_FB2_TicketProcessDepartment" AS "FB2_TicketProcessDepartment",
	"C_FB2_TicketAccountableDepartmentLv1" AS "FB2_TicketAccountableDepartmentLv1",
	"C_FB2_TicketAccountableDepartmentLv2" AS "FB2_TicketAccountableDepartmentLv2",
	"C_FB2_DepartProcessLong" AS "FB2_DepartProcessLong",
	"C_FB2_TicketLong" AS "FB2_TicketLong",
	"C_FB2_BreakAreaCount" AS "FB2_BreakAreaCount",
	"C_FB2_TicketClass" AS "FB2_TicketClass",
	"C_FB2_ProcessUser" AS "FB2_ProcessUser",
	"C_FB2_IsGroupTicket" AS "FB2_IsGroupTicket",
	"C_FB2_GroupTicketId" AS "FB2_GroupTicketId",
	"C_FB2_IsForceClose" AS "FB2_IsForceClose",
	"C_FB2_CloudClass" AS "FB2_CloudClass",
	"C_FB2_TicketCreate" AS "FB2_TicketCreate",
	"C_FB2_TicketSend" AS "FB2_TicketSend",
	"C_FB2_TicketWantFixedTime" AS "FB2_TicketWantFixedTime",
	"C_FB2_ProcOvertimeTime" AS "FB2_ProcOvertimeTime",
	"C_FB2_CloseOvertimeTime" AS "FB2_CloseOvertimeTime",
	"C_FB2_TicketFixedTime" AS "FB2_TicketFixedTime",
	"C_FB2_TicketAccountableAudisTime" AS "FB2_TicketAccountableAudisTime",
	"C_FB2_TicketAudisTime" AS "FB2_TicketAudisTime",
	"C_KD_FixedStatus" AS "KD_FixedStatus",
	"C_KD_Area" AS "KD_Area",
	"C_KD_ProcessUser" AS "KD_ProcessUser"
FROM "A_WorkTickets"."T_WorkTicket"
WHERE "C_TicketId" = %(id)s
LIMIT 1
''', {
			'id': ticket_id
		}
	)
	db.close()

	result = {}
	for item in data['data']:
		i = 0
		for col in data['meta']:
			result[col.name] = item[i]
			i += 1

	return result

@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/get/worker-grid')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
@mousechsh_rest_pager_api_annotation()
def GetWorkerGrid(params):

	pageSize = params['pageSize'] if 'pageSize' in params else 10
	pageIndex = ( params['pageIndex'] if 'pageIndex' in params else 1 )

	db = MouseChshPostgresql()
	db.connect()
	count = db.get(
		'''
SELECT
	COUNT(*) AS "Count"
FROM "A_WorkTickets"."V_WorkerGrid_Worker"
		''', {
		}
	)
	data = db.get(
		'''
SELECT
	"C_Name" AS "Name",
	"C_Area" AS "Area",
	"C_Zone" AS "Zone"
FROM "A_WorkTickets"."V_WorkerGrid_Worker"
ORDER BY "C_Order", "C_Zone", "C_Area", "C_Name" ASC
LIMIT %(pageSize)s OFFSET %(pageIndex)s
''', {
			'pageSize': pageSize,
			'pageIndex': ( ( pageIndex - 1 ) * pageSize if pageIndex - 1 > 0 else 0 )
		}
	)
	db.close()

	result = []
	for item in data['data']:
		obj = {}
		i = 0
		for col in data['meta']:
			obj[col.name] = item[i]
			i += 1
		result.append(obj)

	return {
		'data': result,
		'pageIndex': pageIndex,
		'pageSize': pageSize,
		'totalData': count['data'][0][0]
	}

@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/get/zibaozhang')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
@mousechsh_rest_api_annotation()
def GetZibaozhangReport(params):

	if 'current_month' in params:
		temp = MouseChshDateTime().set_mode_('ym').from_string_( params['current_month'] ).clean_().set_mode_('ymdhms')
		searched_from_time = temp.to_string()
		searched_to_time = temp.add_('month', 1).to_string()
	else:
		temp = MouseChshDateTime().set_mode_('ym').clean_().set_mode_('ymdhms')
		searched_from_time = temp.to_string()
		searched_to_time = temp.add_('month', 1).to_string()

	mousechsh_logging( '查询【', searched_from_time, '】到【', searched_to_time, '】为基准的自报障故障处理响应情况' )

	db = MouseChshPostgresql()
	db.connect()
	data = db.get(
		'''
SELECT
	M."Department",
	M."C1_Count",
	M."C1_Overtime_Count",
	M."C1_Overtime_NotFixed_Count",
	M."C1_OvertimeLong_Count",
	M."C1_Break_Count",
	M."C1_Break_Overtime_Count",
	M."C2_Count",
	M."C2_OverTime_Count",
	M."C2_Youhua_Count",
	M."C2_Youhua_Overtime_Count",
	( M."C1_Count" + M."C2_Count" ) AS "C1C2_Count",
	CASE WHEN M."C1_Count" = 0 THEN '100.00%%' ELSE concat( round(
		( M."C1_Count" - M."C1_Overtime_Count" )::numeric * 100.0 / M."C1_Count",
		2 ), '%%' ) END AS "C1_NotOverTime_p",
	CASE WHEN M."C1_Break_Count" = 0 THEN '100.00%%' ELSE concat( round(
		( M."C1_Break_Count" - M."C1_Break_Overtime_Count" )::numeric * 100.0 / M."C1_Break_Count",
		2 ), '%%' ) END AS "C1_Break_NotOverTime_p",
	CASE WHEN ( M."C2_Count" - M."C2_Youhua_Count" ) = 0 THEN '100.00%%' ELSE concat( round(
		100.0 - ( M."C2_OverTime_Count" - M."C2_Youhua_Overtime_Count" )::numeric / ( M."C2_Count" - M."C2_Youhua_Count" ) * 100.0,
		2 ), '%%' ) END AS "C2_NotOverTime_p"
FROM (
	SELECT
		S."Number",
		S."Department",
		COALESCE( C1Count."Count", 0 ) AS "C1_Count",
		COALESCE( C1OverTime."Count", 0 ) AS "C1_Overtime_Count",
		COALESCE( C1OverTimeNotFixed."Count", 0 ) AS "C1_Overtime_NotFixed_Count",
		COALESCE( C1OverTimeLong."Count", 0 ) AS "C1_OvertimeLong_Count",
		COALESCE( C1Break."Count", 0 ) AS "C1_Break_Count",
		COALESCE( C1BreakOverTime."Count", 0 ) AS "C1_Break_Overtime_Count",
		COALESCE( C2Count."Count", 0 ) AS "C2_Count",
		COALESCE( C2OverTime."Count", 0 ) AS "C2_OverTime_Count",
		COALESCE( C2Youhua."Count", 0 ) AS "C2_Youhua_Count",
		COALESCE( C2YouhuaOverTime."Count", 0 ) AS "C2_Youhua_Overtime_Count"
	FROM (
		SELECT
			"C_Order" AS "Number",
			"C_Name" AS "Department"
		FROM "A_WorkTickets"."V_WorkerGrid_SubCompany"
		ORDER BY "C_Order" ASC
	) S
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*) AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%C1%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C1Count
		ON S."Department" = C1Count."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*) AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%C1%%'
		AND "C_FB2_IsProcOvertime" = 1
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C1OverTime
		ON S."Department" = C1OverTime."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*) AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%C1%%'
		AND "C_FB2_IsProcOvertime" = 1
		AND "C_FB2_TicketStatus" LIKE '%%处理中%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C1OverTimeNotFixed
		ON S."Department" = C1OverTimeNotFixed."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*) AS "Count"
		FROM (
			SELECT
				A."C_FB2_TicketAccountableDepartmentLv2",
				A."C_FB2_TicketLevel",
				A."C_FB2_IsProcOvertime",
				A."C_FB2_TicketStatus",
				A."C_FB2_TicketCreate"
			FROM "A_WorkTickets"."V_WorkTicket_NoFree" A
			LEFT JOIN "A_WorkTickets"."V_WorkerGrid_TicketLevel" B
				ON A."C_FB2_TicketLevel" = B."C_Name"
			WHERE A."C_FB2_DepartProcessLong" > B."C_OverTimeLong"
		) C1OverTimeLongInner
		WHERE "C_FB2_TicketLevel" LIKE '%%C1%%'
		AND "C_FB2_IsProcOvertime" = 1
		AND "C_FB2_TicketStatus" LIKE '%%处理中%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C1OverTimeLong
		ON S."Department" = C1OverTimeLong."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*) AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%断站%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C1Break
		ON S."Department" = C1Break."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*) AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%断站%%'
		AND "C_FB2_IsProcOvertime" = 1
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C1BreakOverTime
		ON S."Department" = C1BreakOverTime."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*)AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%C2%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C2Count
		ON S."Department" = C2Count."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*)AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%C2%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		AND "C_FB2_IsProcOvertime" = 1
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C2OverTime
		ON S."Department" = C2OverTime."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*)AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%C2%%'
		AND "C_FB2_TicketLevel" LIKE '%%优化%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C2Youhua
		ON S."Department" = C2Youhua."C_FB2_TicketAccountableDepartmentLv2"
	LEFT OUTER JOIN (
		SELECT "C_FB2_TicketAccountableDepartmentLv2", COUNT(*)AS "Count"
		FROM "A_WorkTickets"."V_WorkTicket_NoFree"
		WHERE "C_FB2_TicketLevel" LIKE '%%C2%%'
		AND "C_FB2_TicketLevel" LIKE '%%优化%%'
		AND "C_FB2_TicketCreate" >= %(from_time)s
		AND "C_FB2_TicketCreate" < %(to_time)s
		AND "C_FB2_IsProcOvertime" = 1
		GROUP BY "C_FB2_TicketAccountableDepartmentLv2"
		ORDER BY "C_FB2_TicketAccountableDepartmentLv2"
	) C2YouhuaOverTime
		ON S."Department" = C2YouhuaOverTime."C_FB2_TicketAccountableDepartmentLv2"
	ORDER BY "Number"
) M
''', {
			'from_time': searched_from_time,
			'to_time': searched_to_time
		}
	)
	db.close()

	result = []
	total = {
		'Department': '总计',
		'C1_Count': 0,
		'C1_Overtime_Count': 0,
		'C1_Overtime_NotFixed_Count': 0,
		'C1_OvertimeLong_Count': 0,
		'C1_Break_Count': 0,
		'C1_Break_Overtime_Count': 0,
		'C2_Count': 0,
		'C1C2_Count': 0,
		'C2_Youhua_Overtime_Count': 0,
		'C2_Youhua_Count': 0,
		'C2_OverTime_Count': 0
	}
	for item in data['data']:
		obj = {}
		i = 0
		for col in data['meta']:
			obj[col.name] = item[i]
			i += 1
		total['C1_Count'] += obj['C1_Count']
		total['C1_Overtime_Count'] += obj['C1_Overtime_Count']
		total['C1_Overtime_NotFixed_Count'] += obj['C1_Overtime_NotFixed_Count']
		total['C1_OvertimeLong_Count'] += obj['C1_OvertimeLong_Count']
		total['C1_Break_Count'] += obj['C1_Break_Count']
		total['C1_Break_Overtime_Count'] += obj['C1_Break_Overtime_Count']
		total['C2_Count'] += obj['C2_Count']
		total['C1C2_Count'] += obj['C1C2_Count']
		total['C2_Youhua_Overtime_Count'] += obj['C2_Youhua_Overtime_Count']
		total['C2_Youhua_Count'] += obj['C2_Youhua_Count']
		total['C2_OverTime_Count'] += obj['C2_OverTime_Count']
		result.append(obj)
	total['C1_NotOverTime_p'] = str(Decimal(
		(total['C1_Count'] - total['C1_Overtime_Count']) * 100.0 / total['C1_Count']
		).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")) + '%' if total['C1_Count'] else '100%'
	total['C1_Break_NotOverTime_p'] = str(Decimal(
		(total['C1_Break_Count'] - total['C1_Break_Overtime_Count']) * 100.0 / total['C1_Break_Count']
		).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")) + '%' if total['C1_Break_Count'] else '100%'
	total['C2_NotOverTime_p'] = str(Decimal(
		100.0 - (total['C2_OverTime_Count'] - total['C2_Youhua_Overtime_Count']) * 100.0 / (total['C2_Count'] - total['C2_Youhua_Count'])
		).quantize(Decimal("0.01"), rounding="ROUND_HALF_UP")) + '%' if total['C2_Count'] - total['C2_Youhua_Count'] else '100%'
	result.append(total)

	return result


@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/get/no-fixed')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
@mousechsh_rest_api_annotation()
def GetNoFixedTicket(params):

	if 'current_day' in params:
		temp = MouseChshDateTime().set_mode_('ymd').from_string_( params['current_day'] ).clean_().set_mode_('ymdhms')
		searched_from_time = temp.to_string()
		searched_to_time = temp.add_('day', 1).to_string()
		searched_from_month = MouseChshDateTime().from_string_(searched_from_time).set_mode_('ym').clean_().set_mode_('ymdhms').to_string()
	else:
		temp = MouseChshDateTime().set_mode_('ymd').clean_().set_mode_('ymdhms')
		searched_from_time = temp.to_string()
		searched_to_time = temp.add_('day', 1).to_string()
		searched_from_month = MouseChshDateTime().from_string_(searched_from_time).set_mode_('ym').clean_().set_mode_('ymdhms').to_string()

	mousechsh_logging( '查询【', searched_from_month, '】到【', searched_to_time, '】为基准的未恢复故障处理情况统计' )

	db = MouseChshPostgresql()
	db.connect()
	data = db.get(
		'''
SELECT
	T1."Order",
	T1."Name",
	T1."Zone",
	T1."Class",
	T1."TotalTicket",
	T2."UnFixedTicket",
	T3."UnFixedTicketOverTime",
	CASE WHEN T1."TotalTicket" = 0 THEN '100.00%%' ELSE concat( round(
			( T1."TotalTicket" - T2."UnFixedTicket" )::numeric * 100.0 / T1."TotalTicket",
			2 ), '%%' ) END AS "FixedTicket_p"
FROM (
	SELECT
			U."Order",
			U."Name",
			U."Zone",
			U."Class",
			COUNT(ToT."TicketId") AS "TotalTicket"
	FROM (
			SELECT
					"C_Order" AS "Order",
					"C_Name" AS "Name",
					"C_Zone" AS "Zone",
					"C_Class" AS "Class"
			FROM "A_WorkTickets"."V_WorkerGrid_Worker"
			WHERE "C_Order" IS NOT NULL
			ORDER BY "C_Order" ASC
	) U
	LEFT JOIN (
			SELECT
					"C_TicketId" AS "TicketId",
					"C_KD_ProcessUser" AS "ProcessUser"
			FROM "A_WorkTickets"."V_WorkTicket_NoFree"
			WHERE "C_FB2_TicketCreate" >= %(from_time)s
			AND "C_FB2_TicketCreate" < %(to_time)s
	) ToT
			ON U."Name" = ToT."ProcessUser"
	GROUP BY U."Order", U."Name", U."Zone", U."Class"
	ORDER BY "Order" ASC
) T1
LEFT JOIN (
	SELECT
			U."Order",
			U."Name",
			U."Zone",
			U."Class",
			COUNT(UfT."TicketId") AS "UnFixedTicket"
	FROM (
			SELECT
					"C_Order" AS "Order",
					"C_Name" AS "Name",
					"C_Zone" AS "Zone",
					"C_Class" AS "Class"
			FROM "A_WorkTickets"."V_WorkerGrid_Worker"
			WHERE "C_Order" IS NOT NULL
			ORDER BY "C_Order" ASC
	) U
	LEFT JOIN (
			SELECT
					"C_TicketId" AS "TicketId",
					"C_KD_ProcessUser" AS "ProcessUser"
			FROM "A_WorkTickets"."V_WorkTicket_NoFree"
			WHERE "C_FB2_TicketStatus" IN ( '处理中' )
			AND "C_FB2_TicketCreate" >= %(from_time)s
			AND "C_FB2_TicketCreate" < %(to_time)s
	) UfT
			ON U."Name" = UfT."ProcessUser"
	GROUP BY U."Order", U."Name", U."Zone", U."Class"
	ORDER BY "Order" ASC
) T2
	ON T1."Order" = T2."Order" AND T1."Name" = T2."Name" AND T1."Zone" = T2."Zone" AND T1."Class" = T2."Class"
LEFT JOIN (
	SELECT
			U."Order",
			U."Name",
			U."Zone",
			U."Class",
			COUNT(ULT."TicketId") AS "UnFixedTicketOverTime"
	FROM (
			SELECT
					"C_Order" AS "Order",
					"C_Name" AS "Name",
					"C_Zone" AS "Zone",
					"C_Class" AS "Class"
			FROM "A_WorkTickets"."V_WorkerGrid_Worker"
			WHERE "C_Order" IS NOT NULL
			ORDER BY "C_Order" ASC
	) U
	LEFT JOIN (
			SELECT
					"C_TicketId" AS "TicketId",
					"C_KD_ProcessUser" AS "ProcessUser"
			FROM "A_WorkTickets"."V_WorkTicket_NoFree"
			WHERE "C_FB2_TicketStatus" IN ( '处理中' )
			AND "C_FB2_TicketCreate" >= %(from_time)s
			AND "C_FB2_TicketCreate" < %(to_time)s
			AND "C_FB2_IsProcOvertime" = 1
	) ULT
			ON U."Name" = ULT."ProcessUser"
	GROUP BY U."Order", U."Name", U."Zone", U."Class"
	ORDER BY "Order" ASC
) T3
	ON T1."Order" = T3."Order" AND T1."Name" = T3."Name" AND T1."Zone" = T3."Zone" AND T1."Class" = T3."Class"
''', {
			'from_time': searched_from_month,
			'to_time': searched_to_time
		}
	)
	db.close()

	result = []
	for item in data['data']:
		obj = {}
		i = 0
		for col in data['meta']:
			obj[col.name] = item[i]
			i += 1
		result.append(obj)

	return result

@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/upload/excel')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
def UploadExcel(url, request:MouseChshHttpRequest, response, *argsArr, **argsDict):

	file_path = ''

	if request.get_body_type() == 'multipart':
		bodys:MouseChshHttpMultiPartBody = request.get_body()
		file_body = bodys.get_content('file')
		file_name = bodys.get_filename('file')
		file_name_idx = file_name.rfind('.') if file_name else -1
		file_path = mousechsh_math_gen_uuid() + ( file_name[file_name_idx:] if file_name_idx >= 0 else '.xlsx' )
		mousechsh_file_write_binary(mousechsh_configuration_get('file.temp.dir') + file_path, file_body)

	response.get_header().set_content_type('json')
	response.set_body(
		file_path
	)


@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/excel/get/sheets')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
@mousechsh_rest_api_annotation()
def GetExcelSheets(params):
	file_path = params.get('path')
	if not file_path:
		return []

	xls_obj = pd.read_excel( io=mousechsh_configuration_get('file.temp.dir') + file_path, header=None, sheet_name=None, nrows=10, keep_default_na=False )

	result = {
		'list': [],
		'detail': {}
	}
	for sheet_name in xls_obj:
		result['list'].append(sheet_name)
		sheet = xls_obj[sheet_name]
		for j in range(0, len(sheet.index)-1):
			flag = False
			columns = []
			for i in range(0, len(sheet.columns)):
				if sheet[i][j]:
					columns.append({
						'num': i,
						'name': sheet[i][j],
						'linked': sheet[i][j+1],
					})
				else:
					flag = True
					break
			if not flag:
				result['detail'][sheet_name] = columns
				break
	return result


@mousechsh_http_router_annotation(method=['GET', 'POST', 'OPTIONS'], path='/excel/load/sheets')
@mousechsh_cors_annotation()
@mousechsh_http_header_accept_language_annotation()
@mousechsh_rest_api_annotation()
def LoadExcelSheets(params):
	load_mode = params.get('mode')
	file_path = params.get('path')
	if not file_path:
		return 'Fault'
	sheet_name = params.get('sheetname')

	if load_mode == 'fb2bi1':
		LoadExcelSheets_fb2bi1(mousechsh_configuration_get('file.temp.dir') + file_path, sheet_name)
	elif load_mode == 'fb2search':
		LoadExcelSheets_fb2search(mousechsh_configuration_get('file.temp.dir') + file_path, sheet_name)
	elif load_mode == 'kdfb':
		LoadExcelSheets_kdfb(mousechsh_configuration_get('file.temp.dir') + file_path, sheet_name)
	elif load_mode == 'kdsearch':
		LoadExcelSheets_kdsearch(mousechsh_configuration_get('file.temp.dir') + file_path, sheet_name)

	return {
		'status': 'OK'
	}

def checkTicketId( db:MouseChshPostgresql, ticket_id: str ):
	count=db.get(
		'''
SELECT
	count(*) AS "Count"
FROM "A_WorkTickets"."T_WorkTicket" A
WHERE
	"C_TicketId" = %(TicketId)s
			''', {
			'TicketId': str( ticket_id )
		}
	)
	count = count['data'][0][0] * 1
	return count > 0


# 上海联通服务保障系统2.0 - 故障修复及时率统计(部门)
def LoadExcelSheets_fb2bi1(current_filename, current_sheetname):
	db = MouseChshPostgresql()
	db.connect()

	sheet = pd.read_excel( io=current_filename, header=1, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for i in range(0, len(sheet)):
			line = sheet.loc[i]
			if not line['故障单号']:
				continue
			if checkTicketId(db, line['故障单号']):
				mousechsh_logging('更新 故障单号 ', line['故障单号'], ' 故障级别 ', line['故障级别'], ' 故障主题 ', line['故障主题'][0:30])
				db.execute(
					'''
UPDATE "A_WorkTickets"."T_WorkTicket" SET
	"X_Data" = %(data)s,
	"C_FB2_TicketCategory" = %(TicketCategory)s,
	"C_FB2_TicketLevel" = %(TicketLevel)s,
	"C_FB2_IsBreakBusiness" = %(IsBreakBusiness)s,
	"C_FB2_NetworkLevel" = %(NetworkLevel)s,
	"C_FB2_TicketCreate" = %(TicketCreate)s,
	"C_FB2_TicketSend" = %(TicketSend)s,
	"C_FB2_BaseStationId" = %(BaseStationId)s,
	"C_FB2_TicketTitle" = %(TicketTitle)s,
	"C_FB2_TicketContent" = %(TicketContent)s,
	"C_FB2_TicketProcess" = %(TicketProcess)s,
	"C_FB2_TicketReason" = %(TicketReason)s,
	"C_FB2_TicketReasonCategoryLv1" = %(TicketReasonCategoryLv1)s,
	"C_FB2_TicketReasonCategoryLv2" = %(TicketReasonCategoryLv2)s,
	"C_FB2_TicketProcessDepartment" = %(TicketProcessDepartment)s,
	"C_FB2_TicketAccountableDepartmentLv1" = %(TicketAccountableDepartmentLv1)s,
	"C_FB2_TicketAccountableDepartmentLv2" = %(TicketAccountableDepartmentLv2)s,
	"C_FB2_TicketFixedTime" = %(TicketFixedTime)s,
	"C_FB2_TicketWantFixedTime" = %(TicketWantFixedTime)s,
	"C_FB2_DepartProcessLong" = %(DepartProcessLong)s,
	"C_FB2_TicketLong" = %(TicketLong)s,
	"C_FB2_TicketStatus" = %(TicketStatus)s,
	"C_FB2_BreakAreaCount" = %(BreakAreaCount)s,
	"C_FB2_ProcOvertimeTime" = %(ProcOvertimeTime)s,
	"C_FB2_CloseOvertimeTime" = %(CloseOvertimeTime)s,
	"C_FB2_TicketAccountableAudisTime" = %(TicketAccountableAudisTime)s,
	"C_FB2_TicketAudisTime" = %(TicketAudisTime)s,
	"C_FB2_IsProcOvertime" = %(IsProcOvertime)s,
	"C_FB2_IsForceCloseOvertime" = %(IsForceCloseOvertime)s,
	"C_FB2_TicketClass" = %(TicketClass)s,
	"C_FB2_ProcessUser" = %(ProcessUser)s,
	"C_FB2_ReCreateTimes" = %(ReCreateTimes)s,
	"C_FB2_IsGroupTicket" = %(IsGroupTicket)s,
	"C_FB2_GroupTicketId" = %(GroupTicketId)s,
	"C_FB2_CloudClass" = %(CloudClass)s,
	"C_FB2_IsForceClose" = %(IsForceClose)s,
	"C_FB2_IsFree" = %(IsFree)s,
	"C_FB2_IsProjectLeave" = %(IsProjectLeave)s
WHERE
	"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['是否中断业务'] == '是' else 0 ),
						'NetworkLevel': str( line['网络层级'] ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketSend': ( str( line['故障派发时间'] ) if line['故障派发时间'] else None ),
						'BaseStationId': str( line['基站站号'] ),
						'TicketTitle': str( line['故障主题'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketReasonCategoryLv1': str( line['一级原因分类'] ),
						'TicketReasonCategoryLv2': str( line['二级原因分类'] ),
						'TicketProcessDepartment': str( line['故障当前处理部门'] ),
						'TicketAccountableDepartmentLv1': str( line['故障主责部门'] ),
						'TicketAccountableDepartmentLv2': str( line['故障二级主责部门'] ),
						'TicketFixedTime': ( str( line['故障修复时间'] ) if line['故障修复时间'] else None ),
						'TicketWantFixedTime': ( str( line['故障单要求修复时间'] ) if line['故障单要求修复时间'] else None ),
						'DepartProcessLong': str( line['本部门处理历时'] ),
						'TicketLong': str( line['故障总历时'] ),
						'TicketStatus': str( line['工单状态'] ),
						'BreakAreaCount': str( line['中断小区数'] ),
						'ProcOvertimeTime': ( str( line['故障单要求闭环时间'] ) if line['故障单要求闭环时间'] else None ),
						'CloseOvertimeTime': ( str( line['故障回复结束时间'] ) if line['故障回复结束时间'] else None ),
						'TicketAccountableAudisTime': ( str( line['主责人审核结束时间'] ) if line['主责人审核结束时间'] else None ),
						'TicketAudisTime': ( str( line['故障审核结束时间'] ) if line['故障审核结束时间'] else None ),
						'IsProcOvertime': str( 1 if line['处理超时'] == '是' else 0 ),
						'IsForceCloseOvertime': str( 1 if line['闭环超时'] == '是' else 0 ),
						'TicketClass': str( line['专业细分'] ),
						'ProcessUser': str( line['代维名称'] ),
						'ReCreateTimes': str( line['告警反复次数'] if line['告警反复次数'] else 0 ),
						'IsGroupTicket': str( 1 if line['是否集团单'] == '是' else 0 ),
						'GroupTicketId': str( line['集团编号'] ),
						'CloudClass': str( line['云类别'] ),
						'IsForceClose': str( 1 if line['是否强制闭环'] == '是' else 0 ),
						'IsFree': str( 1 if line['是否减免'] == '是' else 0 ),
						'IsProjectLeave': str( 1 if line['是否工程遗留'] == '是' else 0 ),
						'TicketId': str( line['故障单号'] )
					}
				)
			else:
				mousechsh_logging('添加 故障单号 ', line['故障单号'], ' 故障级别 ', line['故障级别'], ' 故障主题 ', line['故障主题'][0:30])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
INSERT INTO "A_WorkTickets"."T_WorkTicket"(
	"C_Id",
	"X_Data",
	"C_TicketId",
	"C_FB2_TicketCategory",
	"C_FB2_TicketLevel",
	"C_FB2_IsBreakBusiness",
	"C_FB2_NetworkLevel",
	"C_FB2_TicketCreate",
	"C_FB2_TicketSend",
	"C_FB2_BaseStationId",
	"C_FB2_TicketTitle",
	"C_FB2_TicketContent",
	"C_FB2_TicketProcess",
	"C_FB2_TicketReason",
	"C_FB2_TicketReasonCategoryLv1",
	"C_FB2_TicketReasonCategoryLv2",
	"C_FB2_TicketProcessDepartment",
	"C_FB2_TicketAccountableDepartmentLv1",
	"C_FB2_TicketAccountableDepartmentLv2",
	"C_FB2_TicketFixedTime",
	"C_FB2_TicketWantFixedTime",
	"C_FB2_DepartProcessLong",
	"C_FB2_TicketLong",
	"C_FB2_TicketStatus",
	"C_FB2_BreakAreaCount",
	"C_FB2_ProcOvertimeTime",
	"C_FB2_CloseOvertimeTime",
	"C_FB2_TicketAccountableAudisTime",
	"C_FB2_TicketAudisTime",
	"C_FB2_IsProcOvertime",
	"C_FB2_IsForceCloseOvertime",
	"C_FB2_TicketClass",
	"C_FB2_ProcessUser",
	"C_FB2_ReCreateTimes",
	"C_FB2_IsGroupTicket",
	"C_FB2_GroupTicketId",
	"C_FB2_CloudClass",
	"C_FB2_IsForceClose",
	"C_FB2_IsFree",
	"C_FB2_IsProjectLeave"
) VALUES (
	%(id)s,
	%(data)s,
	%(TicketId)s,
	%(TicketCategory)s,
	%(TicketLevel)s,
	%(IsBreakBusiness)s,
	%(NetworkLevel)s,
	%(TicketCreate)s,
	%(TicketSend)s,
	%(BaseStationId)s,
	%(TicketTitle)s,
	%(TicketContent)s,
	%(TicketProcess)s,
	%(TicketReason)s,
	%(TicketReasonCategoryLv1)s,
	%(TicketReasonCategoryLv2)s,
	%(TicketProcessDepartment)s,
	%(TicketAccountableDepartmentLv1)s,
	%(TicketAccountableDepartmentLv2)s,
	%(TicketFixedTime)s,
	%(TicketWantFixedTime)s,
	%(DepartProcessLong)s,
	%(TicketLong)s,
	%(TicketStatus)s,
	%(BreakAreaCount)s,
	%(ProcOvertimeTime)s,
	%(CloseOvertimeTime)s,
	%(TicketAccountableAudisTime)s,
	%(TicketAudisTime)s,
	%(IsProcOvertime)s,
	%(IsForceCloseOvertime)s,
	%(TicketClass)s,
	%(ProcessUser)s,
	%(ReCreateTimes)s,
	%(IsGroupTicket)s,
	%(GroupTicketId)s,
	%(CloudClass)s,
	%(IsForceClose)s,
	%(IsFree)s,
	%(IsProjectLeave)s
)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': str( line['故障单号'] ),
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['是否中断业务'] == '是' else 0 ),
						'NetworkLevel': str( line['网络层级'] ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketSend': ( str( line['故障派发时间'] ) if line['故障派发时间'] else None ),
						'BaseStationId': str( line['基站站号'] ),
						'TicketTitle': str( line['故障主题'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketReasonCategoryLv1': str( line['一级原因分类'] ),
						'TicketReasonCategoryLv2': str( line['二级原因分类'] ),
						'TicketProcessDepartment': str( line['故障当前处理部门'] ),
						'TicketAccountableDepartmentLv1': str( line['故障主责部门'] ),
						'TicketAccountableDepartmentLv2': str( line['故障二级主责部门'] ),
						'TicketFixedTime': ( str( line['故障修复时间'] ) if line['故障修复时间'] else None ),
						'TicketWantFixedTime': ( str( line['故障单要求修复时间'] ) if line['故障单要求修复时间'] else None ),
						'DepartProcessLong': str( line['本部门处理历时'] ),
						'TicketLong': str( line['故障总历时'] ),
						'TicketStatus': str( line['工单状态'] ),
						'BreakAreaCount': str( line['中断小区数'] ),
						'ProcOvertimeTime': ( str( line['故障单要求闭环时间'] ) if line['故障单要求闭环时间'] else None ),
						'CloseOvertimeTime': ( str( line['故障回复结束时间'] ) if line['故障回复结束时间'] else None ),
						'TicketAccountableAudisTime': ( str( line['主责人审核结束时间'] ) if line['主责人审核结束时间'] else None ),
						'TicketAudisTime': ( str( line['故障审核结束时间'] ) if line['故障审核结束时间'] else None ),
						'IsProcOvertime': str( 1 if line['处理超时'] == '是' else 0 ),
						'IsForceCloseOvertime': str( 1 if line['闭环超时'] == '是' else 0 ),
						'TicketClass': str( line['专业细分'] ),
						'ProcessUser': str( line['代维名称'] ),
						'ReCreateTimes': str( line['告警反复次数'] if line['告警反复次数'] else 0 ),
						'IsGroupTicket': str( 1 if line['是否集团单'] == '是' else 0 ),
						'GroupTicketId': str( line['集团编号'] ),
						'CloudClass': str( line['云类别'] ),
						'IsForceClose': str( 1 if line['是否强制闭环'] == '是' else 0 ),
						'IsFree': str( 1 if line['是否减免'] == '是' else 0 ),
						'IsProjectLeave': str( 1 if line['是否工程遗留'] == '是' else 0 )
					}
				)
	except Exception as ex:
		mousechsh_logging_exception(ex, '导入【上海联通服务保障系统2.0 - 故障修复及时率统计(部门)】时遇到错误')

	db.commit()
	db.close()


# 上海联通服务保障系统2.0 - 故障查询
def LoadExcelSheets_fb2search(current_filename, current_sheetname):
	db = MouseChshPostgresql()
	db.connect()

	sheet = pd.read_excel( io=current_filename, header=1, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for i in range(0, len(sheet)):
			line = sheet.loc[i]
			if not line['故障单号(必填)']:
				continue
			if checkTicketId(db, line['故障单号(必填)']):
				mousechsh_logging('更新 故障单号(必填) ', line['故障单号(必填)'], ' 故障级别 ', line['故障级别'], ' 故障主题(必填) ', line['故障主题(必填)'][0:30])
				db.execute(
					'''
UPDATE "A_WorkTickets"."T_WorkTicket" SET
	"X_Data" = %(data)s,
	"C_FB2_TicketCategory" = %(TicketCategory)s,
	"C_FB2_TicketLevel" = %(TicketLevel)s,
	"C_FB2_IsBreakBusiness" = %(IsBreakBusiness)s,
	"C_FB2_TicketCreate" = %(TicketCreate)s,
	"C_FB2_TicketTitle" = %(TicketTitle)s,
	"C_FB2_TicketContent" = %(TicketContent)s,
	"C_FB2_TicketProcess" = %(TicketProcess)s,
	"C_FB2_TicketReason" = %(TicketReason)s,
	"C_FB2_TicketProcessDepartment" = %(TicketProcessDepartment)s,
	"C_FB2_TicketAccountableDepartmentLv1" = %(TicketAccountableDepartmentLv1)s,
	"C_FB2_TicketFixedTime" = %(TicketFixedTime)s,
	"C_FB2_TicketStatus" = %(TicketStatus)s,
	"C_FB2_IsFree" = %(IsFree)s
WHERE
	"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['业务是否中断'] == '是' else 0 ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketTitle': str( line['故障主题(必填)'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketProcessDepartment': str( line['故障处理人'] ),
						'TicketAccountableDepartmentLv1': str( line['当前主责部门'] ),
						'TicketFixedTime': ( str( line['业务恢复时间'] ) if line['业务恢复时间'] else None ),
						'TicketStatus': str( line['订单状态'] ),
						'IsFree': str( 1 if line['考核减免'] == '是' else 0 ),
						'TicketId': str( line['故障单号(必填)'] )
					}
				)
			else:
				mousechsh_logging('添加 故障单号(必填) ', line['故障单号(必填)'], ' 故障级别 ', line['故障级别'], ' 故障主题(必填) ', line['故障主题(必填)'][0:30])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
INSERT INTO "A_WorkTickets"."T_WorkTicket"(
	"C_Id",
	"X_Data",
	"C_TicketId",
	"C_FB2_TicketCategory",
	"C_FB2_TicketLevel",
	"C_FB2_IsBreakBusiness",
	"C_FB2_TicketCreate",
	"C_FB2_TicketTitle",
	"C_FB2_TicketContent",
	"C_FB2_TicketProcess",
	"C_FB2_TicketReason",
	"C_FB2_TicketProcessDepartment",
	"C_FB2_TicketAccountableDepartmentLv1",
	"C_FB2_TicketFixedTime",
	"C_FB2_TicketStatus",
	"C_FB2_IsFree"
) VALUES (
	%(id)s,
	%(data)s,
	%(TicketId)s,
	%(TicketCategory)s,
	%(TicketLevel)s,
	%(IsBreakBusiness)s,
	%(TicketCreate)s,
	%(TicketTitle)s,
	%(TicketContent)s,
	%(TicketProcess)s,
	%(TicketReason)s,
	%(TicketProcessDepartment)s,
	%(TicketAccountableDepartmentLv1)s,
	%(TicketFixedTime)s,
	%(TicketStatus)s,
	%(IsFree)s
)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': str( line['故障单号(必填)'] ),
						'TicketCategory': str( line['专业分类'] ),
						'TicketLevel': str( line['故障级别'] ),
						'IsBreakBusiness': str( 1 if line['业务是否中断'] == '是' else 0 ),
						'TicketCreate': ( str( line['故障发生时间'] ) if line['故障发生时间'] else None ),
						'TicketTitle': str( line['故障主题(必填)'] ),
						'TicketContent': str( line['故障描述'] ),
						'TicketProcess': str( line['故障处理描述'] ),
						'TicketReason': str( line['故障准确原因'] ),
						'TicketProcessDepartment': str( line['故障处理人'] ),
						'TicketAccountableDepartmentLv1': str( line['当前主责部门'] ),
						'TicketFixedTime': ( str( line['业务恢复时间'] ) if line['业务恢复时间'] else None ),
						'TicketStatus': str( line['订单状态'] ),
						'IsFree': str( 1 if line['考核减免'] == '是' else 0 )
					}
				)
	except Exception as ex:
		mousechsh_logging_exception(ex, '导入【上海联通服务保障系统2.0 - 故障查询】时遇到错误')

	db.commit()
	db.close()


# 中国联通客户服务支撑系统(科大系统) - 服保综合处理
def LoadExcelSheets_kdfb(current_filename, current_sheetname):
	db = MouseChshPostgresql()
	db.connect()

	sheet = pd.read_excel( io=current_filename, header=0, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for i in range(0, len(sheet)):
			line = sheet.loc[i]
			if not line['业务号码']:
				continue
			if checkTicketId(db, line['业务号码']):
				mousechsh_logging('更新 业务号码 ', line['业务号码'], ' 区域 ', line['区域'], ' 处理人 ', line['处理人'])
				db.execute(
					'''
UPDATE "A_WorkTickets"."T_WorkTicket" SET
	"X_Data" = %(data)s,
	"C_KD_FixedStatus" = %(FixedStatus)s,
	"C_KD_Area" = %(Area)s,
	"C_KD_ProcessUser" = %(ProcessUser)s
WHERE
	"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'FixedStatus': line['网管修复标识'],
						'Area': line['区域'],
						'ProcessUser': line['处理人'],
						'TicketId': line['业务号码']
					}
				)
			else:
				mousechsh_logging('添加 业务号码 ', line['业务号码'], ' 区域 ', line['区域'], ' 处理人 ', line['处理人'])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
INSERT INTO "A_WorkTickets"."T_WorkTicket"(
	"C_Id",
	"X_Data",
	"C_TicketId",
	"C_KD_FixedStatus",
	"C_KD_Area",
	"C_KD_ProcessUser"
) VALUES (
	%(id)s,
	%(data)s,
	%(TicketId)s,
	%(FixedStatus)s,
	%(Area)s,
	%(ProcessUser)s
)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': line['业务号码'],
						'FixedStatus': line['网管修复标识'],
						'Area': line['区域'],
						'ProcessUser': line['处理人']
					}
				)
	except Exception as ex:
		mousechsh_logging_exception(ex, '导入【中国联通客户服务支撑系统(科大系统) - 服保综合处理】时遇到错误')

	db.commit()
	db.close()

# 中国联通客户服务支撑系统(科大系统) - 工单查询
def LoadExcelSheets_kdsearch(current_filename, current_sheetname):
	db = MouseChshPostgresql()
	db.connect()

	sheet = pd.read_excel( io=current_filename, header=0, sheet_name=current_sheetname, keep_default_na=False )
	try:
		for i in range(0, len(sheet)):
			line = sheet.loc[i]
			if not line['业务号码']:
				continue
			if checkTicketId(db, line['业务号码']):
				mousechsh_logging('更新 业务号码 ', line['业务号码'], ' 区域 ', line['区域'], ' 外线处理人 ', line['外线处理人'])
				db.execute(
					'''
UPDATE "A_WorkTickets"."T_WorkTicket" SET
	"X_Data" = %(data)s,
	"C_KD_Area" = %(Area)s,
	"C_KD_ProcessUser" = %(ProcessUser)s
WHERE
	"C_TicketId" = %(TicketId)s
						''', {
						'data': '{}',
						'Area': line['区域'],
						'ProcessUser': line['外线处理人'],
						'TicketId': line['业务号码']
					}
				)
			else:
				mousechsh_logging('添加 业务号码 ', line['业务号码'], ' 区域 ', line['区域'], ' 外线处理人 ', line['外线处理人'])
				new_id = str(uuid4()).upper().replace('-','')
				db.execute(
					'''
INSERT INTO "A_WorkTickets"."T_WorkTicket"(
	"C_Id",
	"X_Data",
	"C_TicketId",
	"C_KD_Area",
	"C_KD_ProcessUser"
) VALUES (
	%(id)s,
	%(data)s,
	%(TicketId)s,
	%(Area)s,
	%(ProcessUser)s
)
						''', {
						'id': new_id,
						'data': '{}',
						'TicketId': line['业务号码'],
						'Area': line['区域'],
						'ProcessUser': line['外线处理人']
					}
				)
	except Exception as ex:
		mousechsh_logging_exception(ex, '导入【中国联通客户服务支撑系统(科大系统) - 工单查询】时遇到错误')

	db.commit()
	db.close()
